package okhttp3Demo.baidu.news.demo;

import com.alibaba.fastjson.JSON;
import com.google.common.collect.Lists;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import okhttp3.OkHttpClient;
import okhttp3.Request;
import okhttp3.Response;
import okhttp3Demo.OkHttpClientFactory;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;
import persistence.common.ConnectionProperties;
import persistence.mysql.MysqlConnection;
import utils.DateUtils;
import utils.StringUtils;
import utils.TxtUtils;

import java.io.File;
import java.io.IOException;
import java.io.PrintWriter;
import java.io.StringWriter;
import java.net.URLEncoder;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalTime;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.concurrent.*;

public class Demo {

    public static String baseDir = "C:\\Users\\Administrator\\Desktop\\companyToNews\\";
    public static ConnectionProperties properties;
    static {
        properties = new ConnectionProperties();
        properties.setServer("localhost");
        properties.setPort(3306);
        properties.setDatabase("data");
        properties.setUsername("root");
        properties.setPassword("");
    }

    public static void main(String[] args) throws Exception {
        String filePath = "C:\\Users\\Administrator\\Desktop\\companyToNews\\data.xls";
        ExecutorService executorService = Executors.newFixedThreadPool(20);
        Demo demo = new Demo();
        List<List<String>> listList =  demo.getCompanyList(filePath);
        for(int i=0;i<listList.size();i++){
            final int index = i;
            executorService.execute(()->{
                new Demo().genNews(listList.get(index),listList.get(index).get(0),index);
            });
        }
        executorService.shutdown();
        while (!executorService.awaitTermination(1, TimeUnit.SECONDS)){
            System.out.println("心跳");
        }
    }

    public void genNews(List<String> companyList,String num,int index){
        Connection connection = MysqlConnection.getConnection(properties);
        try {
            Statement statement =connection.createStatement();
            statement.execute(String.format(create_sql,index));
            for(int i =0;i< companyList.size();i++){
                String company = companyList.get(i);
                List<News> newsList = new ArrayList<>();
                try {
                    System.out.println(String.format("company=%s,开始=%s",company,LocalTime.now().toString()));
                    String html = "";
                    String nextUrl = null;
                    do{
                        html = getNews(company,nextUrl);
                        newsList.addAll(handlerHtml(html,company));
                        Thread.sleep(1000);
                    }while ((nextUrl = nextPage(html))!= null);
                    insert(statement,newsList,index,num,company);
                    System.out.println(String.format("company=%s,结束=%s",company,LocalTime.now().toString()));
                    System.out.println("完成：" + i);
                    String info = String.format("index=%s,company=%s,num=%s,记录=%s\n",index,company,num,newsList.size());
                    TxtUtils.append(new File(baseDir +"complate.txt"),info);
                } catch (Exception e){
                    e.printStackTrace();
                    TxtUtils.append(new File(baseDir + num +"_Error.txt"),company+"\t\n");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private static final String insert_sql = "INSERT INTO `news_news_%s` ( `date`, `str_date`, `author`, `key_word`, `title`, `year`, `url`, `content`) VALUES ( %s, '%s', '%s', '%s', '%s', '%s', '%s', '%s');";
    private static final String create_sql = "CREATE TABLE `news_news_%s` (\n" +
            "  `id` int(11) NOT NULL AUTO_INCREMENT,\n" +
            "  `date` date DEFAULT NULL COMMENT '发布日期',\n" +
            "  `str_date` varchar(32) DEFAULT NULL COMMENT '发布日期',\n" +
            "  `author` varchar(255) DEFAULT NULL COMMENT '作者',\n" +
            "  `key_word` varchar(255) DEFAULT NULL COMMENT '检索关键词',\n" +
            "  `title` varchar(255) DEFAULT NULL COMMENT '新闻标题',\n" +
            "  `year` char(4) DEFAULT NULL COMMENT '发布年份',\n" +
            "  `url` text COMMENT 'URL路径',\n" +
            "  `content` longtext CHARACTER SET utf8mb4 COMMENT '新闻内容',\n" +
            "  `create_at` datetime DEFAULT CURRENT_TIMESTAMP,\n" +
            "  PRIMARY KEY (`id`),\n" +
            "  KEY `idx_company` (`key_word`),\n" +
            "  KEY `idx_date` (`date`)\n" +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
    public void insert(Statement statement,List<News> newsList,int index,String num,String company){
        newsList.forEach(news -> {
            try {
                String sql = String.format(insert_sql
                        , index
                        , news.getDate() == null ? "null" : "'"+DateUtils.formatDate("yyyy-MM-dd HH:mm:ss",news.getDate())+"'"
                        ,news.getStrDate()
                        ,news.getAuthor()
                        ,news.getKeyWord()
                        ,news.getTitle()
                        ,news.getYear()
                        ,news.getUrl()
                        ,handerStr(news.getContent())
                );
                statement.execute(sql);
            } catch (Exception e) {
                System.out.println("### 插入异常：index=" + index + JSON.toJSONString(news));
                e.printStackTrace();
                String sql = String.format(insert_sql
                        , index
                        , news.getDate() == null ? "null" : "'"+DateUtils.formatDate("yyyy-MM-dd HH:mm:ss",news.getDate())+"'"
                        ,news.getStrDate()
                        ,news.getAuthor()
                        ,news.getKeyWord()
                        ,news.getTitle()
                        ,news.getYear()
                        ,news.getUrl()
                        ,handerStr("插入异常："+e.getMessage()));
                try {
                    statement.execute(sql);
                } catch (Exception e1) {
                    System.out.println("### 插入异常异常：index=" + index + ","+sql);
                    e1.printStackTrace();
                }
            }
        });
    }

    public void log_warn(String log){
        TxtUtils.append(new File(baseDir + "log_warn.txt"),log);
    }
    public void log_info(String log){
        TxtUtils.append(new File(baseDir + "log_info.txt"),log);
    }

    public String handerStr(String source){
        if(StringUtils.isNullOrEmpty(source))return source;
        return source.replaceAll("'","\\\\'");
    }

    public String nextPage(String html){
        if(!html.contains("下一页"))return null;
        Element document = Jsoup.parseBodyFragment(html).getElementById("page");
        Element lastA = document.getElementsByTag("a").last();
        if(lastA.text().contains("下一页"))return "https://www.baidu.com" + lastA.attr("href");
        else return null;

    }

    public List<List<String>> getCompanyList(String filePath){
        List<List<String>> listList = new ArrayList<>();
        try {
            List<String> companyList = new ArrayList<>();
            Sheet sheet  = Workbook.getWorkbook(new File(filePath)).getSheet(0);
            Cell[] cells = sheet.getColumn(0);
            for(Cell cell : cells){
                companyList.add(cell.getContents());
            }
            int size = 100;
            for(int c = 0;c<=companyList.size();c=c+size){
                if(c+size>companyList.size() )listList.add(companyList.subList(c,companyList.size()));
                else listList.add(companyList.subList(c,c+size));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return listList;
    }

    public List<News> handlerHtml(String html,String company){
        Element document = Jsoup.parseBodyFragment(html).getElementById("content_left");
        Elements elements = document.getElementsByClass("result");
        List<News> newsList = new ArrayList<>();
        for(Element element : elements){
            Element a = element.getElementsByTag("a").first();
            Element div = element.getElementsByClass("c-author").first();
            String[] attributes = div.text().split(" ");
            News news = new News();
            news.setKeyWord(company);
            news.setTitle(a.text());
            news.setUrl(a.attr("href"));

            if(attributes.length > 0)news.setAuthor(attributes[0].trim());
            if(attributes.length > 1){
                String date = attributes[1].trim();
                news.setStrDate(date);
                date = date.replace("年","-");
                date = date.replace("月","-");
                date = date.replace("日","");
                if(date.length() == 10) {
                    news.setDate(DateUtils.parseData("yyyy-MM-dd",date));
                    news.setYear(DateUtils.formatDate("yyyy",news.getDate()));
                }
            }
            if(!news.getTitle().contains(news.getKeyWord())
                    || news.getUrl().contains("http://finance.sina.com.cn/realstock/")
                    || !isValidDate(news.getDate())){
                log_warn(String.format("### 无效记录：关键词=%s,标题=%s,日期=%s,路径=%s"
                        ,news.getKeyWord()
                        ,news.getTitle()
                        ,news.getStrDate()
                        ,news.getUrl()
                ));
                continue;
            }
            news.setContent(getNewsContent(news.getUrl()));
            newsList.add(news);
            log_info(String.format("### 添加记录：关键词=%s,标题=%s,日期=%s,路径=%s"
                    ,news.getKeyWord()
                    ,news.getTitle()
                    ,news.getStrDate()
                    ,news.getUrl()
            ));
        }
        return newsList;
    }
    public boolean isValidDate(Date date){
        if(date == null)return false;
        int year = Integer.valueOf(DateUtils.formatDate("yyyy",date));
        if(year > 2018 || year < 2014)return false;
        else return true;
    }

    public String getNews(String company,String url) throws Exception{
        if(url == null || url.length() == 0){
            url = new StringBuilder("https://www.baidu.com/s?ie=utf-8&cl=2&medium=1&rtt=1&bsst=1&rsv_dl=news_t_sk&tn=news&word=")
                    .append(URLEncoder.encode(company,"utf-8")).toString();
        }
        System.out.println(url);
        OkHttpClient client = OkHttpClientFactory.getInstance().getClient(OkHttpClientFactory.BAI_DU_NEWS);
        Request request = new Request.Builder().addHeader("User-Agent","Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/68.0.3440.106 Safari/537.36").url(url).get()
                .addHeader("Content-Type","text/html;charset=utf-8")
                .build();
        Response response = client.newCall(request).execute();
        String resultStr = response.body().string();

        if("http://news.baidu.com/ns?word=title%3A%28%E5%8C%97%E4%BA%AC%E7%A7%91%E9%94%90%29&pn=260&cl=2&ct=0&tn=newstitle&rn=20&ie=utf-8&bt=0&et=0&rsv_page=1".equalsIgnoreCase(url)){
            System.out.println(resultStr);
        }
        return resultStr;
    }

    public String getNewsContent(String url){
        try {
            OkHttpClient client = OkHttpClientFactory.getInstance().getClient(OkHttpClientFactory.BAI_DU_NEWS);
            Request request = new Request.Builder().url(url).get()
                    .addHeader("Content-Type","text/html;charset=utf-8")
                    .build();
            Response response = client.newCall(request).execute();
            String result =  response.body().string();
            Elements body = Jsoup.parseBodyFragment(result).getElementsByTag("body");
            return body.get(0).text();
        } catch (IOException e) {
            return "error:" + e.getMessage();
        }
    }

}
